﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace EntityDA
{
    public class SachDB
    {
        //Lấy tất cả sách theo loại
        public List<SachData> GetAllSach(int MaLoai)
        {
            List<SachData> DSSach = new List<SachData>();
            ConnectionDB cn = new ConnectionDB();
            string sql = "";
            if (MaLoai == -1)
                sql = "select * from Sach";
            else
            {
                sql = "select * from Sach where MaLoai=@MaLoai";
                cn.cmd.Parameters.Add(new SqlParameter("@MaLoai", MaLoai));
            }
            cn.cmd.CommandText = sql;
            SqlDataReader reader = cn.cmd.ExecuteReader();
            SachData Sach = null;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Sach = new SachData();
                    if (!reader.IsDBNull(0))
                        Sach.MaSach = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        Sach.TenSach = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        Sach.MaTG = reader.GetInt32(2);
                    if (!reader.IsDBNull(3))
                        Sach.MaNXB = reader.GetInt32(3);
                    if (!reader.IsDBNull(4))
                        Sach.GiaNhap = reader.GetDecimal(4);
                    if (!reader.IsDBNull(5))
                        Sach.GiaBia = reader.GetDecimal(5);
                    if (!reader.IsDBNull(6))
                        Sach.GiaBan = reader.GetDecimal(6);
                    if (!reader.IsDBNull(7))
                        Sach.Hinh = reader.GetString(7);
                    if (!reader.IsDBNull(8))
                        Sach.ThongTin = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                        Sach.MaLoai = reader.GetInt32(9);
                    if (!reader.IsDBNull(10))
                        Sach.LuotXem = reader.GetInt32(10);
                    if (!reader.IsDBNull(11))
                        Sach.SoLuong = reader.GetInt32(11);

                    TacGiaDB TG = new TacGiaDB();
                    Sach.TacGia = TG.GetOneTacGia(Sach.MaTG);
                    LoaiSachDB DB = new LoaiSachDB();
                    Sach.Loai = DB.GetOneLoaiSach(Sach.MaLoai);
                    NhaXuatBanDB NXB = new NhaXuatBanDB();
                    Sach.NXB = NXB.GetOneNXB(Sach.MaNXB);

                    DSSach.Add(Sach);
                }
            }
            return DSSach;
        }

        //Lấy 1 quyển sách theo mã sách
        public SachData GetOneSach(int MaSach)
        {
            ConnectionDB cn = new ConnectionDB();
            string sql = "";
            if (MaSach == -1)
                return null;
            else
            {
                sql = "select * from Sach where MaSach=@MaSach";
                cn.cmd.Parameters.Add(new SqlParameter("@MaSach", MaSach));
            }
            cn.cmd.CommandText = sql;
            SqlDataReader reader = cn.cmd.ExecuteReader();
            SachData Sach = null;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Sach = new SachData();
                    if (!reader.IsDBNull(0))
                        Sach.MaSach = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        Sach.TenSach = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        Sach.MaTG = reader.GetInt32(2);
                    if (!reader.IsDBNull(3))
                        Sach.MaNXB = reader.GetInt32(3);
                    if (!reader.IsDBNull(4))
                        Sach.GiaNhap = reader.GetDecimal(4);
                    if (!reader.IsDBNull(5))
                        Sach.GiaBia = reader.GetDecimal(5);
                    if (!reader.IsDBNull(6))
                        Sach.GiaBan = reader.GetDecimal(6);
                    if (!reader.IsDBNull(7))
                        Sach.Hinh = reader.GetString(7);
                    if (!reader.IsDBNull(8))
                        Sach.ThongTin = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                        Sach.MaLoai = reader.GetInt32(9);
                    if (!reader.IsDBNull(10))
                        Sach.LuotXem = reader.GetInt32(10);
                    if (!reader.IsDBNull(11))
                        Sach.SoLuong = reader.GetInt32(11);
                    TacGiaDB TG = new TacGiaDB();
                    Sach.TacGia = TG.GetOneTacGia(Sach.MaTG);
                    LoaiSachDB DB = new LoaiSachDB();
                    Sach.Loai = DB.GetOneLoaiSach(Sach.MaLoai);
                    NhaXuatBanDB NXB = new NhaXuatBanDB();
                    Sach.NXB = NXB.GetOneNXB(Sach.MaNXB);
                }
            }
            return Sach;
        }

        //Thêm sách mới
        public bool ThemSach(SachData Sach)
        {
            try
            {
                ConnectionDB cn = new ConnectionDB();
                string sql = "insert into Sach(TenSach, MaTG, MaNXB, GiaNhap, GiaBia, GiaBan, HinhAnh, ThongTin, MaLoai, SoLuong)";
                sql += "values(@TenSach, @MaTG, @MaNXB, @GiaNhap, @GiaBia, @GiaBan, @HinhAnh, @ThongTin, @MaLoai, @SoLuong)";
                cn.cmd.Parameters.Add(new SqlParameter("@TenSach", Sach.TenSach));
                cn.cmd.Parameters.Add(new SqlParameter("@MaTG", Sach.MaTG));
                cn.cmd.Parameters.Add(new SqlParameter("@MaNXB", Sach.MaNXB));
                cn.cmd.Parameters.Add(new SqlParameter("@GiaNhap", Sach.GiaNhap));
                cn.cmd.Parameters.Add(new SqlParameter("@GiaBia", Sach.GiaBia));
                cn.cmd.Parameters.Add(new SqlParameter("@GiaBan", Sach.GiaBan));
                cn.cmd.Parameters.Add(new SqlParameter("@HinhAnh", Sach.Hinh));
                cn.cmd.Parameters.Add(new SqlParameter("@ThongTin", Sach.ThongTin));
                cn.cmd.Parameters.Add(new SqlParameter("@MaLoai", Sach.MaLoai));
                cn.cmd.Parameters.Add(new SqlParameter("@SoLuong", Sach.SoLuong));
                cn.cmd.CommandText = sql;
                cn.cmd.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}